package cn.edu.ecut.se2024.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import cn.edu.ecut.se2024.model.Inventory;

public class InventoryDao {

    public List<Inventory> findBySearchCriteria(String itemNumber, double minPrice, double maxPrice, int quantity, LocalDate manufacturingDate, int warehouseNumber, boolean fragile, String searchType) {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Inventory> inventoryList = new ArrayList<>();

        try {
            // Load the SQLServer JDBC driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            // Connect to the database
            con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=inventoryDB", "username", "password");
            // Create a SQL query based on search criteria
            String query = "SELECT * FROM inventory WHERE itemNumber = ? AND price BETWEEN ? AND ? AND quantity >= ? AND manufacturingDate = ? AND warehouseNumber = ? AND fragile = ? AND searchType = ?";
            stmt = con.prepareStatement(query);
            stmt.setString(1, itemNumber);
            stmt.setDouble(2, minPrice);
            stmt.setDouble(3, maxPrice);
            stmt.setInt(4, quantity);
            stmt.setDate(5, java.sql.Date.valueOf(manufacturingDate));
            stmt.setInt(6, warehouseNumber);
            stmt.setBoolean(7, fragile);
            stmt.setString(8, searchType);

            // Execute the query
            rs = stmt.executeQuery();
            // Extract data from the result set
            while (rs.next()) {
                Inventory inventory = new Inventory();
                inventory.setItemNumber(rs.getString("itemNumber"));
                inventory.setPrice(rs.getDouble("price"));
                inventory.setQuantity(rs.getInt("quantity"));
                inventory.setManufacturingDate(rs.getDate("manufacturingDate"));
                inventory.setFragile(rs.getBoolean("fragile"));
                inventoryList.add(inventory);
            }
            
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
               if (rs != null) {
                   rs.close();
               }
               if (stmt != null) {
                   stmt.close();
               }
               if (con != null) {
                   con.close();
               }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return inventoryList;
    }
}